
#include "eventrecords.h"
#include "devices_collect.h"
#include "hlog.h"

using json = nlohmann::json;

namespace zx {
SINGLETON_IMPL(EventRecords)
void EventRecords::open(std::string path) {
    try {
        m_conn.open(path);
        m_conn.execute(R"(
            CREATE TABLE IF NOT EXISTS events (
                id        INTEGER PRIMARY KEY,
                type      INTEGER NOT NULL,
                level     INTEGER NOT NULL,
                timestamp INTEGER NOT NULL,
                content   TEXT    NOT NULL,
                plate     TEXT    NOT NULL,
                videopath TEXT,
                imagepath TEXT,
                invalid   INTEGER DEFAULT (0)
            );
        )");

        m_conn.execute(R"(
            CREATE INDEX IF NOT EXISTS idx_events_plate ON events(plate);
        )");

        m_conn.execute(R"(
            CREATE INDEX IF NOT EXISTS idx_events_type ON events(type);
        )");

        m_conn.execute(R"(
            CREATE INDEX IF NOT EXISTS idx_events_level ON events(level);
        )");

        m_conn.execute(R"(
            CREATE INDEX IF NOT EXISTS idx_events_timestamp ON events(timestamp);
        )");

        m_conn.execute(R"(
            CREATE TABLE IF NOT EXISTS records (
                id         INTEGER PRIMARY KEY,
                plate      TEXT    NOT NULL,
                position   TEXT NOT NULL,
                path   TEXT    NOT NULL,
                timestamp  INTEGER NOT NULL,
                start_time INTEGER NOT NULL,
                end_time   INTEGER NOT NULL
            );
        )");

        m_conn.execute(R"(
            CREATE INDEX IF NOT EXISTS records_timestamp_idx ON records (timestamp);
        )");

        m_conn.execute(R"(
            CREATE INDEX IF NOT EXISTS records_plate_idx ON records (plate);
        )");

        m_conn.execute(R"(
            CREATE INDEX IF NOT EXISTS records_position_idx ON records (position);
        )");

        m_conn.execute(R"(
            CREATE TABLE IF NOT EXISTS cars (
                id      INTEGER PRIMARY KEY,
                department VARCHAR(100),
                user VARCHAR(100),
                plate VARCHAR(100) NOT NULL UNIQUE,
                timestamp BIGINT,
                attr TEXT,
                ip_addr VARCHAR(100),
                devices TEXT
            );
        )");
        m_conn.execute(R"(
            CREATE INDEX IF NOT EXISTS idx_cars_plate_user_department ON cars(plate, user, department);
        )");

        // TODO somethings
        cars_t item;
        cars_query_t cars_list = cars_get(item, 1, cars_count(item) + 1);

        for (size_t i = 0; i < cars_list.data.size(); ++i) {
            cars_t node                = cars_list.data[i];
            device_collect[node.plate] = {
                {"heartbeat", false                    },
                {"online",    false                    },
                {"devices",   json::parse(node.devices)}
                // {"attr",      json::parse(node.attr)   }
            };
        }

    } catch (const std::exception& e) {
        hloge(" open error : %s", e.what());
        throw;
    }
}

void EventRecords::close(void) {
    m_conn.close();
}

void EventRecords::events_add(events_t& item) {
    const char* stmt = R"(
        INSERT INTO events (type, timestamp, plate, content, level, videopath, imagepath) VALUES (?, ?, ?, ?, ? ,? ,?);
    )";
    m_conn.execute(
        stmt, item.type, item.timestamp, item.plate, item.content, item.level, item.videopath, item.imagepath);
}

int EventRecords::events_count(int min_type, int max_type, std::string plate0, int level0, uint64_t start_time,
                               uint64_t end_time) {
    const char* stmt = R"(
        SELECT COUNT(*) FROM events 
            WHERE (plate = ? OR ? = '')
            AND (type = ? OR ? = -1)
            AND (level = ? OR ? = -1)
            AND (? = 0 OR timestamp >= ?)
            AND (? = 0 OR timestamp <= ?);
    )";

    std::string plate = plate0.empty() ? "" : plate0;
    int level         = level0 <= 0 ? -1 : level0;

    auto r = m_conn.execute<std::tuple<int>>(
        stmt, plate, plate, min_type, max_type, level, level, start_time, start_time, end_time, end_time);
    // for (const auto& [count] : r) {
    //     fprintf(stderr, "count: %d\n", count);
    // }
    int cnt = std::get<0>(r.at(0));
    return cnt;
}

event_query_t EventRecords::events_get(int min_type, int max_type, std::string plate0, int level0, uint64_t start_time,
                                       uint64_t end_time, int page_num, int page_size) {
    int page_num_  = page_num <= 0 ? 1 : page_num;
    int page_size_ = page_size <= 0 ? 10 : page_size;

    event_query_t ret = {0};
    std::vector<events_t> msg;

    ret.count = events_count(min_type, max_type, plate0, level0, start_time, end_time);
    if (ret.count == 0) {
        ret.data = msg;
        return ret;
    }
    const char* stmt = R"(
        SELECT * FROM events 
            WHERE (plate = ? OR ? = '')
            AND type BETWEEN ? AND ?
            AND (level = ? OR ? = -1)
            AND (? = 0 OR timestamp >= ?)
            AND (? = 0 OR timestamp <= ?)        
            ORDER BY timestamp DESC, id DESC
            LIMIT ? OFFSET ?;
    )";

    std::string plate = plate0.empty() ? "" : plate0;
    int level         = level0 <= 0 ? -1 : level0;

    auto r =
        m_conn.execute<std::tuple<int, int, int, uint64_t, std::string, std::string, std::string, std::string, int>>(
            stmt,
            plate,
            plate,
            min_type,
            max_type,
            level,
            level,
            start_time,
            start_time,
            end_time,
            end_time,
            page_size_,
            page_size_ * (page_num_ - 1));

    for (const auto& [qid, qtype, qlevel, qtimestamp, qcontent, qplate, qvideopath, qimagepath, qinvalid] : r) {
        events_t node = {
            .id        = qid,
            .type      = qtype,
            .timestamp = qtimestamp,
            .plate     = qplate,
            .content   = qcontent,
            .level     = qlevel,
            .videopath = qvideopath,
            .imagepath = qimagepath,
        };
        msg.emplace_back(node);
        hlogi("%d, %d, %d, %d, %s,  %s, %s, %s, %d",
              qid,
              qtype,
              qlevel,
              qtimestamp,
              qcontent.c_str(),
              qplate.c_str(),
              qvideopath.c_str(),
              qimagepath.c_str(),
              qinvalid);
    }
    ret.data = msg;
    return ret;
}  // namespace zx

void EventRecords::events_set_invalid(std::string id) {
    const char* stmt = R"(UPDATE events SET invalid = 1 WHERE id = ?;)";
    m_conn.execute(stmt, id);
}

void EventRecords::events_delete(std::string id) {
    const char* stmt = R"(DELETE FROM events WHERE id = ? ;)";
    m_conn.execute(stmt, id);
}

std::map<int, int>
EventRecords::statistics_count(int min_type, int max_type, std::string plate, uint64_t start_time, uint64_t end_time) {
    const char* stmt = R"(
        SELECT type, COUNT(*) AS event_count
            FROM events
            WHERE timestamp BETWEEN ? AND ?
            AND type BETWEEN ? AND ?
            AND invalid = 0
            AND (plate = ? OR ? = '')
            GROUP BY type
            ORDER BY type
    )";

    auto r = m_conn.execute<std::tuple<int, int>>(stmt, start_time, end_time, min_type, max_type, plate, plate);

    std::map<int, int> stat;
    for (const auto& [type, count] : r) {
        stat[type] = count;
    }

    // for (auto it = stat.begin(); it != stat.end(); ++it) {
    //     fprintf(stderr, "count: %d: %d\n", it->first, it->second);
    // }
    return stat;
}

std::string
EventRecords::statistics_get(int min_type, int max_type, std::string plate, uint64_t start_time, uint64_t end_time) {
    const char* stmt = R"(
        SELECT json_group_array(
            json_object(
                'range', json_object(
                    'starttime', ?,
                    'endtime', ?
                ),
                'stats', (
                    SELECT json_group_array(
                        json_object(
                            'type', e.type,
                            'count', e.event_count
                        )
                    )
                    FROM (
                        SELECT type, COUNT(*) AS event_count
                        FROM events
                        WHERE timestamp BETWEEN ? AND ?
                        AND type BETWEEN ? AND ?
                        AND invalid = 0
                        AND (plate = ? OR ? = '')
                        GROUP BY type
                        ORDER BY type
                    ) e
                )
            )
        )AS result;
    )";

    auto r = m_conn.execute<std::tuple<std::string>>(
        stmt, start_time, end_time, start_time, end_time, min_type, max_type, plate, plate);

    std::string ret = std::get<0>(r.at(0));
    // hlogi("statistics_get: %s", ret.c_str());
    return ret;
}

void EventRecords::records_add(records_t& item) {
    const char* stmt = R"(
        INSERT INTO records(position, plate, end_time, path, start_time, timestamp) 
        VALUES (?, ?, ?, ?, ?, ?);
    )";
    m_conn.execute(stmt, item.position, item.plate, item.end_time, item.path, item.start_time, item.timestamp);
}

int EventRecords::records_count(std::string plate0, std::string position0, uint64_t start_time, uint64_t end_time) {
    const char* stmt = R"(
        SELECT COUNT(*) FROM records
            WHERE (plate = ? OR ? = '')
            AND (position = ? OR ? = '')
            AND (? = 0 OR timestamp >= ?)
            AND (? = 0 OR timestamp <= ?);
    )";

    std::string plate    = plate0.empty() ? "" : plate0;
    std::string position = position0.empty() ? "" : position0;

    auto r = m_conn.execute<std::tuple<int>>(
        stmt, plate, plate, position, position, start_time, start_time, end_time, end_time);
    int cnt = std::get<0>(r.at(0));
    // fprintf(stderr, "count: %d\n", cnt);
    return cnt;
}

records_query_t EventRecords::records_get(std::string plate0, std::string position0, uint64_t start_time,
                                          uint64_t end_time, int page_num, int page_size) {
    int page_num_       = page_num <= 0 ? 1 : page_num;
    int page_size_      = page_size <= 0 ? 10 : page_size;
    records_query_t ret = {0};
    std::vector<records_t> msg;

    ret.count = records_count(plate0, position0, start_time, end_time);
    if (ret.count == 0) {
        ret.data = msg;
        return ret;
    }

    const char* stmt = R"(
        SELECT * FROM records
            WHERE (plate = ? OR ? = '')
            AND (position = ? OR ? = '')
            AND (? = 0 OR timestamp >= ?)
            AND (? = 0 OR timestamp <= ?)       
            ORDER BY timestamp DESC
            LIMIT ? OFFSET ?;
    )";

    std::string plate    = plate0.empty() ? "" : plate0;
    std::string position = position0.empty() ? "" : position0;
    auto r = m_conn.execute<std::tuple<int, std::string, std::string, std::string, uint64_t, uint64_t, uint64_t>>(
        stmt,
        plate,
        plate,
        position,
        position,
        start_time,
        start_time,
        end_time,
        end_time,
        page_size_,
        page_size_ * (page_num_ - 1));

    for (const auto& [qid, qplate, qposition, qpath, qtimestamp, qstart_time, qend_time] : r) {
        records_t node = {
            .position   = qposition,
            .plate      = qplate,
            .path       = qpath,
            .start_time = qstart_time,
            .end_time   = qend_time,
            .timestamp  = qtimestamp,
        };
        msg.emplace_back(node);
        // hlogi("%d, %s,  %s, %s, %d, %d, %d",
        //       qid,
        //       qplate.c_str(),
        //       qposition.c_str(),
        //       qpath.c_str(),
        //       qtimestamp,
        //       qstart_time,
        //       qend_time);
    }
    ret.data = msg;
    return ret;
}

void EventRecords::cars_add(cars_t& item) {
    const char* stmt = R"(
        INSERT OR REPLACE INTO cars (department, user, plate, timestamp, attr, ip_addr, devices) 
        VALUES (?, ?, ?, ?, ?, ?, ?);
    )";
    m_conn.execute(stmt, item.department, item.user, item.plate, item.timestamp, item.attr, item.ip_addr, item.devices);
}

int EventRecords::cars_count(cars_t& item) {
    const char* stmt       = R"(
        SELECT COUNT(*) FROM cars
            WHERE (plate = ? OR ? = '')
            AND (user = ? OR ? = '')
            AND (department = ? OR ? = '');
    )";
    std::string plate      = item.plate.empty() ? "" : item.plate;
    std::string user       = item.user.empty() ? "" : item.user;
    std::string department = item.department.empty() ? "" : item.department;
    auto r                 = m_conn.execute<std::tuple<int>>(stmt, plate, plate, user, user, department, department);
    int cnt                = std::get<0>(r.at(0));
    // fprintf(stderr, "count: %d\n", cnt);
    return cnt;
}

cars_query_t EventRecords::cars_get(cars_t& item, int page_num, int page_size) {
    int page_num_    = page_num <= 0 ? 1 : page_num;
    int page_size_   = page_size <= 0 ? 10 : page_size;
    cars_query_t ret = {0};
    std::vector<cars_t> msg;

    ret.count = cars_count(item);
    if (ret.count == 0) {
        ret.data = msg;
        return ret;
    }

    const char* stmt = R"(
        SELECT * FROM cars
            WHERE (plate = ? OR ? = '')
            AND (user = ? OR ? = '')
            AND (department = ? OR ? = '')
            ORDER BY timestamp DESC, id DESC
            LIMIT ? OFFSET ?;;
    )";

    std::string plate      = item.plate.empty() ? "" : item.plate;
    std::string user       = item.user.empty() ? "" : item.user;
    std::string department = item.department.empty() ? "" : item.department;
    auto r                 = m_conn.execute<
                        std::tuple<int, std::string, std::string, std::string, uint64_t, std::string, std::string, std::string>>(
        stmt, plate, plate, user, user, department, department, page_size_, page_size_ * (page_num_ - 1));

    for (const auto& [qid, qdepartment, quser, qplate, qtimestamp, qattr, qip_addr, qdevices] : r) {
        cars_t node = {
            .department = qdepartment,
            .user       = quser,
            .plate      = qplate,
            .timestamp  = qtimestamp,
            .attr       = qattr,
            .ip_addr    = qip_addr,
            .devices    = qdevices,
        };
        msg.emplace_back(node);
        // hlogi("query: %d, %s, %s, %s, %d, %s, %s, %s",
        //       qid,
        //       qdepartment.c_str(),
        //       quser.c_str(),
        //       qplate.c_str(),
        //       qtimestamp,
        //       qattr.c_str(),
        //       qip_addr.c_str(),
        //       qdevices.c_str());
    }
    ret.data = msg;
    return ret;
}

}  // namespace zx